//
//  ALPDateBaseManager.m
//  AlpFM
//
//  Created by KING on 16/1/28.
//  Copyright © 2016年 Alp. All rights reserved.
//

#import "ALPDateBaseManager.h"
#import "FMDatabase.h"
#import "FMDatabaseQueue.h"

//沙盒路径
#define  DOCUMENT  [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject]
@interface ALPDateBaseManager()
@property (nonatomic, retain) FMDatabase *fmdb;


@end

@implementation ALPDateBaseManager
#pragma mark - 创建数据库
+(instancetype)shareFMDB{
    static ALPDateBaseManager *fmdb = nil;
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        fmdb = [[ALPDateBaseManager alloc] init];
    });
    return fmdb;
}

#pragma mark - 打开数据库
- (void)openDB{
    NSString *filePath = [DOCUMENT stringByAppendingPathComponent:@"AlpFM.sqlite"];
    NSLog(@"数据库路径:%@", filePath);
    //初始化
    self.fmdb = [[FMDatabase alloc] initWithPath:filePath];
    BOOL result = [self.fmdb open];
    if (result) {
        NSLog(@"=->OPEN DB Succeed");
    } else {
        NSLog(@"=->OPEN DB Failure");
    }
    
}

#pragma mark - 创建收藏专辑的表
- (void)createAlbumsTable{
    NSString *sql = @"CREATE TABLE IF NOT EXISTS albums(number INTEGER PRIMARY KEY AUTOINCREMENT, trackID TEXT, intro TEXT, albumCoverUrl290 TEXT, title TEXT, tracksCounts TEXT, playsCounts TEXT, lastUptrackAt TEXT, userName TEXT)";
    //执行
    [self executeSqlWithStr:sql showLog:@"CREATE ALBUMS TABLE"];
    
}

#pragma mark - 插入专辑对象
- (void)insertAlbum:(ALPAlbumModel *)album andUserName:(NSString *)name{
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO albums(trackID, intro, albumCoverUrl290, title, tracksCounts, playsCounts, lastUptrackAt, userName) VALUES('%@','%@','%@','%@','%@','%@','%@','%@')", album.trackID, album.intro, album.albumCoverUrl290, album.title, album.tracksCounts, album.playsCounts, album.lastUptrackAt, name];
    //执行语句
    [self executeSqlWithStr:sql showLog:@"INSERT Album"];
}

#pragma mark - 删除专辑对象(根据title)
- (void)deleteAlbumWithTitle:(NSString *)title{
    NSString *sql = [NSString stringWithFormat:@"DELETE FROM albums WHERE title = '%@'", title];
    //执行语句
    [self executeSqlWithStr:sql showLog:@"DELETE"];
}
#pragma mark - 查询专辑对象(所有)
- (NSMutableArray *)selectALbumDataWhereUserName:(NSString *)userName{
    NSMutableArray *array = [NSMutableArray array];
    
    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM albums WHERE userName = '%@'", userName];
    //执行查询语句
    FMResultSet *set = [self.fmdb executeQuery:sql];
    //FMResultSet 是用来遍历表中符合要求的每一行
    while ([set next]) {
        NSString *set_trackID = [set objectForColumnName:@"trackID"];
        NSString *set_intro = [set objectForColumnName:@"intro"];
        NSString *set_albumCoverUrl290 = [set objectForColumnName:@"albumCoverUrl290"];
        NSString *set_title = [set objectForColumnName:@"title"];
        NSString *set_tracksCounts = [set objectForColumnName:@"tracksCounts"];
        NSString *set_playsCounts = [set objectForColumnName:@"playsCounts"];
        NSString *set_lastUptrackAt = [set objectForColumnName:@"lastUptrackAt"];
        
        ALPAlbumModel *model = [[ALPAlbumModel alloc] init];
        model.trackID = set_trackID;
        model.intro = set_intro;
        model.albumCoverUrl290 = set_albumCoverUrl290;
        model.title = set_title;
        model.tracksCounts = set_tracksCounts;
        model.playsCounts = set_playsCounts;
        model.lastUptrackAt = set_lastUptrackAt;
        
        [array addObject:model];
    }
    
    return array;
}

//title:标题
//coverLarge  背景图片
//playUrl32 :音频地址132
//playUrl64 :音频地址164
//duration:   时长.秒
//"nickname": “晓光Jeanne", 作者栏的名字
//smallLogo:  作者栏的小头像
//albumTitle

//创建播放历史的表
//标题  图片  副标题  最后播放时间  song_ID
#pragma mark - 创建历史表格
- (void)createHistoryTable{
    NSString *sql = @"CREATE TABLE IF NOT EXISTS history(number INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, coverLarge TEXT, nickname TEXT, lastTime TEXT, song_ID TEXT)";
    //执行
    [self executeSqlWithStr:sql showLog:@"CREATE HISTORY TABLE"];
    
}

#pragma mark - 插入历史记录
- (void)insertHistory:(ALPSingleModel *)playerModel andLastTime:(NSString *)time andSong_ID:(NSString *)song_ID{
    //    NSLog(@"插入历史记录");
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO history(title, coverLarge, nickname, lastTime, song_ID) VALUES('%@','%@','%@','%@','%@')", playerModel.title, playerModel.coverLarge, playerModel.nickname, time, song_ID];
    [self executeSqlWithStr:sql showLog:@"INSERT History"];
    
}

#pragma mark - 更新历史数据(最后播放时间)
- (void)upDataHistoryLastTime:(NSString *)time WithSongID:(NSString *)song_ID{
    //    NSLog(@"更新历史记录");
    NSString *sql = [NSString stringWithFormat:@"UPDATE history SET lastTime = '%@' WHERE song_ID = '%@'", time, song_ID];
    [self executeSqlWithStr:sql showLog:@"UPDATE"];
    
}

#pragma mark - 查询历史记录
- (NSMutableArray *)seleteHistory{
    NSMutableArray *array = [NSMutableArray array];
    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM history"];
    //执行查询语句
    FMResultSet *set = [self.fmdb executeQuery:sql];
    while ([set next]) {
        NSString *set_title = [set objectForColumnName:@"title"];
        NSString *set_coverLarge = [set objectForColumnName:@"coverLarge"];
        NSString *set_nickname = [set objectForColumnName:@"nickname"];
        NSString *set_lastTime = [set objectForColumnName:@"lastTime"];
        NSString *set_song_ID = [set objectForColumnName:@"song_ID"];
        
        ALPHistoryModel *model = [[ALPHistoryModel alloc] init];
        model.title = set_title;
        model.coverLarge = set_coverLarge;
        model.nickname = set_nickname;
        model.lastTime = set_lastTime;
        model.song_ID = set_song_ID;
        [array addObject:model];
    }
    
    return array;
}

#pragma mark - 根据指定ID删除
- (void)deleteHistoryWithSong_ID:(NSString *)song_id{
    NSString *sql = [NSString stringWithFormat:@"DELETE FROM history WHERE song_ID = '%@'", song_id];
    //执行语句
    [self executeSqlWithStr:sql showLog:@"DELETE"];
    
    
}

#pragma mark - 清空历史记录
- (void)deleteHistory{
    NSString *sql = @"DELETE FROM history";
    //执行语句
    [self executeSqlWithStr:sql showLog:@"DELETE"];
}





#pragma mark - 封装方法
- (void)executeSqlWithStr:(NSString *)sql showLog:(NSString *)message{
    //执行语句
    BOOL result = [self.fmdb executeUpdate:sql];
    NSString *string = @"";
    if (result) {
        string = [NSString stringWithFormat:@"%@ Succeed", message];
    } else {
        string = [NSString stringWithFormat:@"%@ Failure", message];
    }
    //    NSLog(@"=->%@", string);
}



@end